{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Cannot set a frame with no defined index and a value that cannot be converted to a Series",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_ensure_valid_index\u001b[1;34m(self, value)\u001b[0m\n\u001b[0;32m   3172\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3173\u001b[1;33m                 \u001b[0mvalue\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSeries\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3174\u001b[0m             \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, data, index, dtype, name, copy, fastpath)\u001b[0m\n\u001b[0;32m    276\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 277\u001b[1;33m                 \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSingleBlockManager\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfastpath\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    278\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, block, axis, do_integrity_check, fastpath)\u001b[0m\n\u001b[0;32m   4676\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mblock\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mBlock\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4677\u001b[1;33m             \u001b[0mblock\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmake_block\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mblock\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mplacement\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mslice\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mndim\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4678\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals.py\u001b[0m in \u001b[0;36mmake_block\u001b[1;34m(values, placement, klass, ndim, dtype, fastpath)\u001b[0m\n\u001b[0;32m   3204\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3205\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mklass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mndim\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mndim\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mplacement\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mplacement\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3206\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, values, placement, ndim)\u001b[0m\n\u001b[0;32m   2302\u001b[0m         super(ObjectBlock, self).__init__(values, ndim=ndim,\n\u001b[1;32m-> 2303\u001b[1;33m                                           placement=placement)\n\u001b[0m\u001b[0;32m   2304\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, values, placement, ndim)\u001b[0m\n\u001b[0;32m    124\u001b[0m                 \u001b[1;34m'Wrong number of items passed {val}, placement implies '\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 125\u001b[1;33m                 '{mgr}'.format(val=len(self.values), mgr=len(self.mgr_locs)))\n\u001b[0m\u001b[0;32m    126\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: Wrong number of items passed 7, placement implies 0",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-4025d618d506>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     73\u001b[0m             \u001b[1;32mcontinue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     74\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0m__name__\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'__main__'\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 75\u001b[1;33m     \u001b[0mwh\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvar\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'TA'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mstart\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'20140101'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mend\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'20150105'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     76\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mwh\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-1-4025d618d506>\u001b[0m in \u001b[0;36mwh\u001b[1;34m(var, start, end)\u001b[0m\n\u001b[0;32m     48\u001b[0m             ['date_x', 'variety_x', 'mem_x', 'long_openIntr_x', 'short_openIntr_x', '净持仓_x', '净持仓_y']].dropna()\n\u001b[0;32m     49\u001b[0m         \u001b[1;31m# print(all_position)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 50\u001b[1;33m         \u001b[0mall_position\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'净持仓变化量'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mall_position\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'净持仓_x'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m-\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'净持仓_y'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     51\u001b[0m         \u001b[1;31m# print(all_position)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     52\u001b[0m         \u001b[1;31m# 更名\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__setitem__\u001b[1;34m(self, key, value)\u001b[0m\n\u001b[0;32m   3117\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3118\u001b[0m             \u001b[1;31m# set column\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3119\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3120\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3121\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_set_item\u001b[1;34m(self, key, value)\u001b[0m\n\u001b[0;32m   3191\u001b[0m         \"\"\"\n\u001b[0;32m   3192\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3193\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3194\u001b[0m         \u001b[0mvalue\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3195\u001b[0m         \u001b[0mNDFrame\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_ensure_valid_index\u001b[1;34m(self, value)\u001b[0m\n\u001b[0;32m   3173\u001b[0m                 \u001b[0mvalue\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSeries\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3174\u001b[0m             \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3175\u001b[1;33m                 raise ValueError('Cannot set a frame with no defined index '\n\u001b[0m\u001b[0;32m   3176\u001b[0m                                  \u001b[1;34m'and a value that cannot be converted to a '\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3177\u001b[0m                                  'Series')\n",
      "\u001b[1;31mValueError\u001b[0m: Cannot set a frame with no defined index and a value that cannot be converted to a Series"
     ]
    }
   ],
   "source": [
    "# encoding: utf-8\n",
    "import pymongo,json\n",
    "import pandas as pd\n",
    "from pandas import Series,DataFrame\n",
    "from scipy.stats import pearsonr\n",
    "\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "def wh(var='NI',start='20190105',end='20190627'):\n",
    "    client = pymongo.MongoClient('localhost', 27017)\n",
    "    futures = client.futures\n",
    "    unit = futures.unit\n",
    "    position = futures.position\n",
    "    market = futures.indexMarket\n",
    "    positions = futures.positions\n",
    "\n",
    "    market = DataFrame(list(market.find({'date': {'$gte': start}})))\n",
    "    position = DataFrame(list(position.find({'date': {'$gte': start}})))\n",
    "\n",
    "    # 整理期货公司\n",
    "    party_name = position[position['date'] >= end]\n",
    "    party_name = party_name[party_name['variety'] == var]\n",
    "    long_party_name = party_name['long_party_name']\n",
    "    short_party_name = party_name['short_party_name']\n",
    "    party_name = long_party_name.append(short_party_name)\n",
    "    party_name = party_name.drop_duplicates(keep='first').dropna()\n",
    "    long = position.groupby(['date', 'variety', 'long_party_name'])[['long_openIntr']].sum()\n",
    "    short = position.groupby(['date', 'variety', 'short_party_name'])[['short_openIntr']].sum()\n",
    "    # 合并\n",
    "    frames = [long, short]\n",
    "    position = pd.concat(frames, axis=1, sort=True).fillna(0).reset_index()\n",
    "    # 净持仓\n",
    "    position['净持仓'] = position.apply(lambda x: x['long_openIntr'] - x['short_openIntr'], axis=1)\n",
    "    # 字段更名\n",
    "    position = position.rename(columns={'level_0': 'date', 'level_1': 'variety', 'level_2': 'mem'})\n",
    "    vars = position[position['variety'] == var]\n",
    "    df = {}\n",
    "    for i in party_name:\n",
    "        mem = vars[vars['mem'] == i]\n",
    "        # print(mem)\n",
    "        position_behind = mem.shift(1)\n",
    "        # # 合并滞后和原始数据\n",
    "        all_position = pd.merge(position, position_behind, right_index=True, left_index=True)\n",
    "        # print(all_position)\n",
    "        all_position = all_position[\n",
    "            ['date_x', 'variety_x', 'mem_x', 'long_openIntr_x', 'short_openIntr_x', '净持仓_x', '净持仓_y']].dropna()\n",
    "        # print(all_position)\n",
    "        all_position['净持仓变化量'] = all_position.apply(lambda x: x['净持仓_x'] - x['净持仓_y'], axis=1)\n",
    "        # print(all_position)\n",
    "        # 更名\n",
    "        all_position = all_position.rename(\n",
    "            columns={'date_x': 'date', 'variety_x': 'variety', 'mem_x': 'mam', 'long_openIntr_x': 'long_openIntr',\n",
    "                     'short_openIntr_x': 'short_openIntr', '净持仓_x': '当日净持仓', '净持仓_y': '昨日净持仓'})\n",
    "\n",
    "        # # #涨跌数\n",
    "        market['change'] = market.apply(lambda x: x['set_close'] - x['set_open'], axis=1)\n",
    "        vars1 = market[market['variety'] == var]\n",
    "        chg = vars1[['date', 'variety', 'change']]\n",
    "        # # 合并\n",
    "        hb = pd.merge(chg, all_position, on=['date', 'variety'], how='outer').dropna().drop_duplicates()\n",
    "        chgs = hb['change']\n",
    "        nets = hb['净持仓变化量']\n",
    "\n",
    "        todayNetpostion = all_position[all_position['date']==end]\n",
    "        # #相关系数\n",
    "        p = pearsonr(chgs, nets)[0]\n",
    "        r = pearsonr(chgs, nets)[1]\n",
    "        try:\n",
    "            print(var,start,end,i,p,r,all_position['净持仓变化量'].count(),todayNetpostion['当日净持仓'].values[0],all_position['净持仓变化量'].values[0])\n",
    "        except:\n",
    "            continue\n",
    "if __name__ == '__main__':\n",
    "    wh(var='TA',start='20140101',end='20150105')\n",
    "    print(wh)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "大\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
